// sous module specfique de gestion des acces a la table LOGEMENT
// Table : Logement(idlogt, idproprio, quartier, type, adresse, loyer)
package bdd;

import java.sql.*;
import java.util.*;
import beans.*;

public class DB_LOGEMENT {

  Connection conn;
  PreparedStatement ps_select;
  PreparedStatement ps_insert;
  PreparedStatement ps_update;
  PreparedStatement ps_delete;

  public DB_LOGEMENT(Connection conn) {
     this.conn=conn;
     try{
        ps_select = conn.prepareStatement("select idproprio,quartier,type,adresse,loyer from Logement where idlogt=?");
  	ps_insert = conn.prepareStatement("insert into Logement values(?,?,?,?,?,?)");
        ps_update = conn.prepareStatement("update Logement set idproprio=?, quartier=?,  type=?, adresse=?, loyer=? where idlogt=?");
        ps_delete = conn.prepareStatement("delete from Logement where idlogt=?"); 
     } catch(SQLException ex){System.out.println(ex);}
  }
      
  public Logement getLogement(int idlogt) {
     Logement p=null;
     try{
      ps_select.setInt(1,idlogt);
      ResultSet rs = ps_select.executeQuery();
      if(rs.next()){
          int idproprio = rs.getInt("idproprio");
	  String quartier = rs.getString("quartier");
	  String typeLogt = rs.getString("type");
	  String adresse = rs.getString("adresse");
	  double loyer = rs.getDouble("loyer");
          p = new Logement(idlogt,idproprio,quartier,typeLogt,adresse,loyer);        
	    }
     } catch(SQLException ex){ System.out.println(ex);}
     return p;
  }

  public void insertLogement(Logement p){
		try{
			
			ps_insert.setInt(1,p.getIdlogt());
			ps_insert.setInt(2,p.getIdproprio());
			ps_insert.setString(3,p.getQuartier());
			ps_insert.setString(4,p.getTypelogt());
			ps_insert.setString(5,p.getAdresse());
			ps_insert.setDouble(6,p.getLoyer());
			ps_insert.executeUpdate();
			
		} catch(SQLException ex){System.out.println(ex);}  
  }
        
  public void updateLogement(Logement p){
		try{ 
			ps_update.setInt(1,p.getIdproprio());
			ps_update.setString(2,p.getQuartier());
			ps_update.setString(3,p.getTypelogt());
			ps_update.setString(4,p.getAdresse());
			ps_update.setDouble(5,p.getLoyer());
			ps_update.setInt(6,p.getIdlogt());
			ps_update.executeUpdate();
		} catch(SQLException ex){System.out.println(ex);}     
  }

  public void deleteLogement(int idlogt){
        try{
		ps_delete.setInt(1,idlogt);
		ps_delete.executeUpdate();
     	      } catch(SQLException ex){System.out.println(ex);} 
  }

  public ArrayList<Logement> getLogements(String req) {
	// pre-condition: req est de la forme "SELECT * FROM Logement ..."
	// car il s'agit d'extraire un ensemble de Logements

        Logement p;
	ArrayList<Logement> alp = null;

	try{ 
		alp = new ArrayList<Logement>(); 
		Statement st = conn.createStatement(); 
		ResultSet rs = st.executeQuery(req); 
		while(rs.next()){ 
			p = new Logement(rs.getInt("idlogt"), rs.getInt("idProprio"), rs.getString("quartier"), rs.getString("type"), rs.getString("adresse"), rs.getDouble("loyer")); 
			alp.add(p); 
		} 
	} catch(SQLException ex){System.out.println(ex);} 
	return alp;
  }

  public ArrayList<Logement> getLogements() {
	return getLogements("select * from Logement");
  }


}
